"""
Case Type   : openGauss 支持忽略PLSQL间的依赖进行创建
Case Name   : 方式三设置参数behavior_compat_options参数为plpgsql_dependency
Create At   : 2023.08.07
Owner       : zhanghuan96
Description :
    1. alter database方式设置参数值
    2. alter user方式设置参数值
    3. alter user方式设置恢复参数值
    4. 清理环境
Expect      :
    1. 成功
    2. 成功
    3. 成功
    4. 成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class PlpgsqlDependency(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.common = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.db_name = "db_guc0003"
        self.user_name = "user_guc0003"
        self.default_value = self.common.show_param('behavior_compat_options')

    def test_guc(self):
        text = '--step1:alter database方式设置参数值;expect:成功--'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name};" \
                  f"alter database {self.db_name} set " \
                  f"behavior_compat_options to plpgsql_dependency;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE DATABASE', sql_res, '查询失败')
        self.assertIn('ALTER DATABASE', sql_res, '查询失败')
        self.log.info(f'使用{self.db_name}数据库连接查询')
        sql_cmd = "show behavior_compat_options"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual('plpgsql_dependency',
                         sql_res.splitlines()[-2].strip(),
                         '查询失败')

        self.log.info(f'恢复{self.db_name}数据库参数值')
        sql_cmd = f"alter database {self.db_name} set " \
                  f"behavior_compat_options to default;"
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ALTER DATABASE', sql_res, '查询失败')

        text = '--step2:alter user方式设置参数值;expect:成功--'
        self.log.info(text)
        sql_cmd = f"drop user if exists {self.user_name};" \
                  f"create user {self.user_name} with sysadmin password " \
                  f"'{macro.COMMON_PASSWD}';" \
                  f"alter user {self.user_name} set behavior_compat_options " \
                  f"to plpgsql_dependency;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE ROLE', sql_res, '查询失败')
        self.assertIn('ALTER ROLE', sql_res, '查询失败')
        connect_user = f'-U {self.user_name} ' \
                       f'-W {macro.COMMON_PASSWD}'
        self.log.info(f'使用{self.user_name}用户连接查询')
        sql_cmd = "show behavior_compat_options"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, sql_type=connect_user)
        self.log.info(sql_res)
        self.assertEqual('plpgsql_dependency',
                         sql_res.splitlines()[-2].strip(),
                         '查询失败')

        text = '--step3:alter user方式设置恢复参数值;expect:成功--'
        self.log.info(text)
        sql_cmd = f"alter user {self.user_name} set " \
                  f"behavior_compat_options to '';"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('ALTER ROLE', sql_res, '查询失败')

    def tearDown(self):
        text = '----step4:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};" \
                  f"drop user {self.user_name};"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('DROP DATABASE', sql_res, '删除失败')
        self.assertIn('DROP ROLE', sql_res, '删除失败')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
